package guoan.repository;

import guoan.domain.ExamItemStatus;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface ExamItemStatusRepository extends PagingAndSortingRepository<ExamItemStatus, Long>,
		JpaSpecificationExecutor<ExamItemStatus> {

	ExamItemStatus findByExamIdAndOpenHours(Long examId, Long openHours);

	@Modifying
	@Query(value = "DELETE FROM exam_item_status WHERE exam_id = ?", nativeQuery = true)
	void deleteByExamId(Long id);

	@Modifying
	@Query(value = "UPDATE exam_item_status eis, exam SET"
			+ " eis.applicants_number = eis.applicants_number + 1"
			+ " WHERE eis.exam_id = exam.id"
			+ " AND eis.applicants_number < ifnull(exam.join_no, 0)"
			+ " AND exam.id = ?1"
			+ " AND eis.open_hours = ?2", nativeQuery = true)
	int updateAppNum(Long examId, Long openHours);

}
